#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Software: PyCharm
# @Time    : 2020/3/26 19:28
# @Author  : linjinting
# @Site    : 
# @Software: tools-gzpw
# @File    : generate_sql.py
# @Function:


class SqlGenerate(object):
    @staticmethod
    def mysql_gen_filter_exp(filter_, arg_=None, sql_filter_=None, clauseShip=' AND '):
        if arg_ is None:
            arg_ = []
        filters_ = []
        for key, val, symbol in filter_:
            if isinstance(val, int):
                filter_ = "`%s` %s %%d" % (key, symbol)
            else:
                filter_ = "`%s` %s %%s" % (key, symbol)

            if isinstance(val, tuple):
                arg_.extend(val)
            else:
                arg_.append(val)
            filters_.append(filter_)
        expression_ = clauseShip.join(filters_)

        if sql_filter_ is None:
            return expression_, arg_
        else:
            return sql_filter_ + clauseShip + expression_, arg_

    @staticmethod
    def mysql_gen_assign_exp(*args, **kwargs):
        expressions = []

        def gen_express(field, value):
            # set field = value
            if isinstance(value, int):
                express_ = "`%s` = %d" % (field, value)
            else:
                express_ = "`%s` = '%s'" % (field, value)
            return express_

        for arg in args:
            for field, value in arg.items():
                expressions.append(gen_express(field, value))
        for field, value in kwargs.items():
            expressions.append(gen_express(field, value))

        assign_exp = ', '.join(expressions)

        return assign_exp

    @staticmethod
    def mysql_gen_select(sql_, filter_=None):
        if filter_ is None:
            return sql_
        return sql_ + ' where ' + filter_

    @staticmethod
    def mysql_gen_update(sql_, assign_, filter_):
        return sql_ + ' set ' + assign_ + ' where ' + filter_

    @staticmethod
    def mysql_gen_delete(sql_, filter_=None):
        if filter_ is None:
            return sql_
        return sql_ + ' where ' + filter_

    @staticmethod
    def mysql_gen_insert(sql_, *args, **kwargs):
        expressions = list()
        vals = list()
        for arg in args:
            for field, value in arg.items():
                try:
                    index = expressions.index(field)
                except:
                    index = -1
                if index != -1:
                    raise Exception("field repetition ")
                expressions.append("`%s`" % field)
                vals.append(value)

        for field, value in kwargs.items():
            try:
                index = expressions.index(field)
            except:
                index = -1
            if index != -1:
                raise Exception("field repetition ")
            expressions.append("`%s`" % field)
            vals.append(value)

        count_ = len(expressions)
        if count_ == 0:
            return
        # sql_express = str(tuple(expressions))
        sql_express = ", ".join(expressions)
        sql_field_place = ", ".join(["%s" for i in range(0, count_)])
        return "%s (%s) value (%s)" % (sql_, sql_express, sql_field_place), vals

    @staticmethod
    def mysql_gen_insert_multiple(sql_header_, valuesList):
        # values_multiple = list()
        # count__ = 0
        # for values in valuesList:
        #     count__ = len(values)
        #     for val in values:
        #         if isinstance(val, int):
        #             values_multiple.append(val)
        #         elif val is None:
        #             values_multiple.append(None)
        #         else:
        #             values_multiple.append("%s" % val)
        if len(valuesList[0]) == 0:
            return
        sql_values_place = ", ".join(["%s" for _ in valuesList[0]])

        return "%s  values (%s)" % (sql_header_, sql_values_place), valuesList

    @staticmethod
    def mysql_gen_select_header(table, fields_=None):
        if fields_ is None:
            sql_header_ = "select * from `%s`" % table
            return sql_header_
        if isinstance(fields_, list) and len(fields_) > 0:
            sql_field_place = ", ".join(["`%s`" % field for field in fields_])
            sql_header_ = "select %s from `%s`" % (sql_field_place, table)
            # print(sql_header_)
            return sql_header_
        return None

    @staticmethod
    def mysql_gen_update_header(table):
        # if isinstance(fields_, list):
        #     sql_assign = mysql_gen_assign_exp(*fields_)
        # elif isinstance(fields_, dict):
        #     sql_assign = mysql_gen_assign_exp(**fields_)
        # else:
        #     return
        return "update `%s` " % table

    @staticmethod
    def mysql_gen_insert_header(table, filedList=None):
        if not filedList:
            return "insert into `%s` " % table

        field_total = ", ".join(["`%s`" % field for field in filedList])
        return "insert into `%s`(%s)" % (table, field_total)

        # expressions_field.append("`%s`" % field)

    @staticmethod
    def mysql_gen_delete_header(table):
        return "delete from `%s` " % table


if __name__ == '__main__':
    fields = ["id", "sourceID", "dataTime"]
    sql_header = SqlGenerate.mysql_gen_select_header("t_device", fields)
    filters_ = [["operationTicketID", "optID", "="], ["operationTicketID", "optID", "="]]
    filter_, val = SqlGenerate.mysql_gen_filter_exp(filters_)

    sql = SqlGenerate.mysql_gen_select(sql_header, filter_)
    print(sql)

    assign_ = {"sourceID": 11111}
    sql_header = SqlGenerate.mysql_gen_update_header("t_device")
    sql_assign1 = SqlGenerate.mysql_gen_assign_exp({"sourceID": 11111, "source": "asdfe"}, {"source": "asdfe"})

    sql = SqlGenerate.mysql_gen_update(sql_header, sql_assign1, filter_)
    print(sql)

    sql_header = SqlGenerate.mysql_gen_insert_header("t_bay")
    sql, vals = SqlGenerate.mysql_gen_insert(sql_header, {"sourceID": 11111, "source": "asdfe"}, {"source": "asdfe"})
    print(sql, vals)

    sql_header = SqlGenerate.mysql_gen_delete_header("t_device")
    sql = SqlGenerate.mysql_gen_delete(sql_header, filter_)
    print(sql)
